package com.zx.oa.dao.marketClass;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.dao.census.CensusDao;
import com.zx.oa.dao.census.CensusDaolpml;
import com.zx.oa.entity.Major;
import com.zx.oa.entity.MarketClass;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MarketClassDaoImpl extends BaseDao implements MarketClassDao{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    @Override
    public List<MarketClass> getMarketClassList(String classType,int currentPageNo,int pageSize) {
        List<MarketClass> MarketClassList=new ArrayList<>();
        conn=this.getConnection();
        List<Object> params = new ArrayList<>();
        StringBuffer sql = new StringBuffer("SELECT mkClassId,className,classType FROM marketclass");
        if (classType!=null&&!"".equals(classType)){
            sql.append(" where  `classType` = ?");
            params.add(classType);
        }
        sql.append(" LIMIT ?,?");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);
        conn = this.getConnection();
        try {
            pstmt=conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject((i + 1), params.get(i));
            }
            rs=pstmt.executeQuery();
            while (rs.next()){
                Integer id=rs.getInt("mkClassId");
                String name=rs.getString("className");
                String type=rs.getString("classType");
                MarketClass marketClass=new MarketClass(id,name,type);
                MarketClassList.add(marketClass);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return MarketClassList;
    }

    @Override
    public boolean addMarketClass(String className, String classType) {
        String sql = "INSERT INTO marketclass(className,classType) VALUES(?,?)";
        Object[] params = {className, classType};
        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public MarketClass getMarketClassLists(int classId) {
        MarketClass marketClass=null;
        conn=this.getConnection();

        String sql = "SELECT mkClassId,className,classType FROM marketclass where  `mkClassId` = ?";


        try {
            pstmt=conn.prepareStatement(sql.toString());
            pstmt.setObject(1, classId);

            rs=pstmt.executeQuery();
            while (rs.next()){
                Integer id=rs.getInt("mkClassId");
                String name=rs.getString("className");
                String type=rs.getString("classType");
                 marketClass=new MarketClass(id,name,type);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return marketClass ;
    }

    @Override
    public boolean modifyMarketClass(String name, String classType, int classId) {
        String sql = "UPDATE marketclass SET className=?,classType=? WHERE mkClassId=?";

        Object[] params = {name, classType, classId};
        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean deleteMarketClass(int classId) {
        String sql = "DELETE FROM marketclass WHERE mkClassId=?";
        Object[] params = {classId};
        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public List<MarketClass> getMarketClassType() {
        List<MarketClass> classTypeList=new ArrayList();
        conn=this.getConnection();
        String sql= "SELECT DISTINCT classType FROM marketclass";

        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                String type = rs.getString("classType");
                MarketClass marketClass = new MarketClass(type);
                classTypeList.add(marketClass);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return classTypeList;
    }

    @Override
    public int getMarketClassIdCount(int classId) {
        int count=0;
        conn=this.getConnection();
        String sql= "SELECT count(*) FROM marketstudent where mkClassId=?";

        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1,classId);
            rs=pstmt.executeQuery();
            while (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }

        return count;
    }

    @Override
    public int getClassInfoTotalCount(String type) {
        int count=-1;//总记录数
        //可变长度字符串

        StringBuffer sb = new StringBuffer("select count(*) " +
                " FROM marketclass " +
                " where 1=1 ");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();

        if(null!=type&&!"".equals(type)){
            sb.append(" AND classType`=?");
            params.add(type);
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }
    @Override
    public List<MarketClass> getMarketClassInfo() {
        List<MarketClass> marketClassList=new ArrayList<>();
        conn=this.getConnection();
        String sql="SELECT * FROM marketclass";
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                Integer id=rs.getInt(1);
                String name=rs.getString(2);
                String type=rs.getString(3);
                MarketClass marketClass=new MarketClass(id,name,type);
                marketClassList.add(marketClass);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return marketClassList;
    }

    @Override
    public List<String> getClassTypeAll() {
        List<String> list=new ArrayList<>();
        conn=this.getConnection();
        String sql="SELECT DISTINCT classType FROM marketclass";
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                list.add(rs.getString(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
}
